package com.wondersgroup.tjfx.bo.impl.sqlExecuter;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.cache.annotation.Cacheable;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quick.framework.boot.remoteResult.ResultObject;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.web.utils.SpringContextUtils;
import com.wondersgroup.tjfx.bo.inter.sqlExecuter.ISqlExecuter;
import com.wondersgroup.tjfx.common.utils.FormatterDate;
import com.wondersgroup.tjfx.dao.inter.YdzgDataSqlBeanDao;

@Service("sqlExecuter")
public class SqlExecuterImpl implements ISqlExecuter {
	private static Logger logger = Logger.getLogger(SqlExecuterImpl.class);
	@Autowired
	YdzgDataSqlBeanDao ydzgDataSqlBeanDao;

	@Override
//	@Cacheable(value = "queryBySql1", keyGenerator = "wiselyKeyGenerator")
	public String queryBySql(String sql,String type, Map<String, String> map) throws Exception {
		sql = getSql(sql, map);
		return queryBySql(sql,type);
	}
	@Override
//	@Cacheable(value = "queryByPageSql", keyGenerator = "wiselyKeyGenerator")
	public String queryByPageSql(String sql,String type, Map<String, String> map) throws Exception {
		sql = getPageSql(sql, map);
		return queryBySql(sql,type);
	}

	@Override
	public JSONObject queryByDatagridSql(String sql, Map<String, String> map) throws Exception {
		String pageSql = getPageSql(sql, map);
		String countSql = getCountSql(sql, map);
		SQLBean pageSb = new SQLBean(pageSql);
		SQLBean countSb = new SQLBean(countSql);
		JSONArray rows = ydzgDataSqlBeanDao.queryList(pageSb);
		int count = ydzgDataSqlBeanDao.queryCount(countSb);
		JSONObject data = new JSONObject(2);
		data.put("rows", rows);
		data.put("total", count);
		return data;
	}

	@Override
	public JSONObject queryByTreegridSql(String sql, Map<String, String> map) throws Exception {
		String pageSql = getPageSql(sql, map);
		String countSql = getCountSql(sql, map);
		SQLBean pageSb = new SQLBean(pageSql);
		SQLBean countSb = new SQLBean(countSql);
		JSONArray rows = ydzgDataSqlBeanDao.queryList(pageSb);
		int count = ydzgDataSqlBeanDao.queryCount(countSb);
		JSONObject data = new JSONObject(2);
		data.put("rows", rows);
		data.put("total", count);
		return data;
	}

	@Override
//	@Cacheable(value = "queryBySql2", keyGenerator = "wiselyKeyGenerator")
	public String queryBySql(String sql, Map<String, String> map) {
		ResultObject ro = new ResultObject();
		if (StringUtils.isEmpty(sql)) {
			ro.changeFaultState("sql为空");
		} else {
			try {
				sql = getSql(sql, map);
				SQLBean sb = new SQLBean(sql);
				JSONArray ary = ydzgDataSqlBeanDao.queryList(sb);
				ro.setInfo("data", ary.toString());
			} catch (SQLException e) {
				e.printStackTrace();
				ro.changeFaultState(e.getLocalizedMessage());
			} catch (Exception e) {
				e.printStackTrace();
				ro.changeFaultState(e.getLocalizedMessage());
			}
		}
		return ro.toJsonStr();
	}

	@Override
//	@Cacheable(value = "queryBySql3", keyGenerator = "wiselyKeyGenerator")
	public String queryBySql(String sql, String type) {
		JSONObject json = new JSONObject();
		if (StringUtils.isEmpty(sql)) {
			json.put("state", "false");
			json.put("faultInfo", "sql为空");
			return json.toJSONString();
		}
		SQLBean sb = new SQLBean(sql);
		try {
			@SuppressWarnings("unchecked")
			ResultSetExtractor<JSONObject> rs = (ResultSetExtractor<JSONObject>) SpringContextUtils.getBean(type);
			json = ydzgDataSqlBeanDao.query(sb, rs);
		} catch (DataAccessException e) {
			json.put("state", "false");
			json.put("faultInfo", e.getLocalizedMessage());
			e.printStackTrace();
		}
		return json.toString();
	}

	/**
	 * 获取sql
	 * 
	 * @param sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	private String getSql(String sql, Map<String, String> map) throws Exception {
		if (StringUtils.isEmpty(sql))
			return "";
		try {
			/** 处理%号问题 */
			sql = sql.replaceAll("%(?![0-9a-fA-F]{2})", "%25");
			sql = URLDecoder.decode(sql, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		sql = sql.replaceAll("\\s*=\\s*", "=");
		sql = validateDateParams(sql, map);
		sql = autoComplateDate(sql);
		sql = autoComplateParam(sql, map);
		return sql;
	}

	/**
	 * 获取分页sql
	 * 
	 * @param sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	private String getPageSql(String sql, Map<String, String> map) throws Exception {
		sql = getSql(sql, map);
		Integer minnumber = Integer.valueOf(map.remove("minnumber"));
		Integer maxnumber = Integer.valueOf(map.remove("maxnumber"));
	
		StringBuffer s = new StringBuffer();
		s.append("SELECT * ");
		s.append("  FROM (SELECT A.*, ROWNUM R ");
		s.append("          FROM ( ");
		s.append(sql);
		s.append(") A) ");
		s.append("where r > " + minnumber);
		s.append("  and r<=" + maxnumber);
		return s.toString();
	}
	private String getCountSql(String sql, Map<String, String> map) throws Exception {
		sql = getSql(sql, map);
		StringBuffer s = new StringBuffer();
		s.append("SELECT COUNT(1) AS COUNT ");
		s.append("  FROM ( ");
		s.append(sql);
		s.append(") ");
		return s.toString();
	}

	/**
	 * 校验/替换开始、结束时间参数
	 * 
	 * @param sql
	 * @param map
	 * @return
	 * @throws Exception
	 */
	private String validateDateParams(String sql, Map<String, String> map) throws Exception {
		boolean isMissingStartDate = sql.indexOf("@startDate") > -1 && !map.containsKey("startDate");
		boolean isMissingEndDate = sql.indexOf("@endDate") > -1 && !map.containsKey("endDate");
		if (isMissingStartDate || isMissingEndDate) {
			throw new Exception("缺少时间参数");
		}
		sql = sql.replaceAll("@startDate", map.get("startDate"));
		sql = sql.replaceAll("@endDate", map.get("endDate"));
		return sql;
	}

	/**
	 * 根据sql中的时间参数格式，替换为对应的时间<br>
	 * 时间参数共16个
	 * 
	 * @param sql
	 * @return
	 */
	private String autoComplateDate(String sql) {

		Map<String, String> map = FormatterDate.getFormatterDate();
		Iterator<Entry<String, String>> iterator = map.entrySet().iterator();
		while (iterator.hasNext()) {
			Entry<String, String> en = iterator.next();
			String key = en.getKey();
			String value = en.getValue();
			sql = sql.replaceAll("@" + key, value);
		}
		return sql;
	}

	/**
	 * 匹配sql中的参数变量，替换参数拼装sql
	 * 
	 * @param sql
	 * @param map
	 * @return
	 */
	private String autoComplateParam(String sql, Map<String, String> map) {
		map = mapKeyToUpper(map);
		//替换所有等号判断条件，没有值的参数，条件替换成1=1
		sql = autoComplateParamEqulas(sql, map);
		//直接替换剩余的所有参数
		sql = autoComplateParamOthers(sql, map);
		return sql;
	}

	private String autoComplateParamEqulas(String sql, Map<String, String> map) {
		String reg = "\\S*=\\'{0,1}@([a-zA-Z.-9]*)\\'{0,1}";
		Pattern pattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(sql);
		while (matcher.find()) {
			String group = matcher.group();// ssqxdm='@ssqxdm'
			String var = matcher.group(1);// ssqxdm
			if (map.containsKey(var.toUpperCase())) {
				String value = map.get(var.toUpperCase());
				if (StringUtils.isEmpty(value)) {
					sql = sql.replaceAll(group, "1=1");
				} else {
					sql = sql.replaceAll("@" + var, value);
				}
			} else {
				sql = sql.replaceAll(group, "1=1");
			}
		}
		return sql;
	}

	private String autoComplateParamOthers(String sql, Map<String, String> map) {
		String reg = "\\'{0,1}@([a-zA-Z.-9]*)\\'{0,1}";
		Pattern pattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);
		Matcher matcher = pattern.matcher(sql);
		while (matcher.find()) {
			String var = matcher.group(1);// YLJGDM
			String value = map.get(var.toUpperCase());//000000000
			value = value == null ? "" : value;
			sql = sql.replaceAll("@" + var, value);
		}
		return sql;
	}

	/**
	 * 将map的key转成大写
	 * 
	 * @param map
	 * @return
	 */
	private Map<String, String> mapKeyToUpper(Map<String, String> map) {
		Map<String, String> upperKeyMap = new HashMap<String, String>();
		Iterator<Entry<String, String>> iterator = map.entrySet().iterator();
		while (iterator.hasNext()) {
			Entry<String, String> entry = iterator.next();
			String key = entry.getKey();
			Object value = entry.getValue();
			upperKeyMap.put(key.toUpperCase(), value + "");
		}
		return upperKeyMap;
	}

}
